package edu.unsw.comp9321.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.unsw.comp9321.common.ServiceLocatorException;
import edu.unsw.comp9321.exception.EmptyResultException;

public class CarDAOImpl implements CarDAO {

	private Connection connection;
	
	public CarDAOImpl() throws ServiceLocatorException, SQLException {
		connection = DBConnectionFactory.getConnection();
	}
	
	@Override
	public List<CarDTO> findAllCars() {
		List<CarDTO> list = new ArrayList<CarDTO>();
		try {
			Statement stmnt = connection.createStatement();
			String query = "SELECT * FROM car";
			ResultSet res = stmnt.executeQuery(query);
			while (res.next()) {
				int id = res.getInt("car_id");
				int type = res.getInt("car_type");
				list.add(new CarDTO(id, type));
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public CarDTO findCarWithId(String carId) throws EmptyResultException{
		CarDTO car = null;
		try {
			String query = "SELECT * FROM car WHERE car_id = ?";
			PreparedStatement stmnt = connection.prepareStatement(query);
			stmnt.setString(1, carId);
			ResultSet res = stmnt.executeQuery();
			while (res.next()) {
				int id = res.getInt("car_id");
				int type = res.getInt("car_type");
				car = new CarDTO(id,type); 
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return car;
	}

	@Override
	public List<CarDTO> findCars(String bodyType, boolean isAutomatic) {
		List<CarDTO> cars = new ArrayList<CarDTO>();
		try {
			String query = "SELECT * " +
					"FROM car_type t " +
					"LEFT JOIN car c ON t.car_type_id = c.car_type " +
					"WHERE t.body_type = ? AND t.is_automatic = ?;";
			PreparedStatement stmnt = connection.prepareStatement(query);
			stmnt.setString(1, bodyType);
			stmnt.setBoolean(2, isAutomatic);
			ResultSet res = stmnt.executeQuery();
			while (res.next()) {
				int id = res.getInt("car_id");
				int type = res.getInt("car_type");
				cars.add(new CarDTO(id, type));
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return cars;
	}

	@Override
	public CarTypeDTO findCarTypeWithCarId(int carId) {
		CarTypeDTO carType = null;
		try {
			String query = "SELECT * " +
					"FROM car_type t " +
					"LEFT JOIN car c ON t.car_type_id = c.car_type " +
					"WHERE c.car_id = ?;";
			PreparedStatement stmnt = connection.prepareStatement(query);
			stmnt.setString(1, String.valueOf(carId));
			ResultSet res = stmnt.executeQuery();
			while (res.next()) {
				int id = res.getInt("car_type_id");
				String typeClass = res.getString("class");
				String make_model = res.getString("make_model");
				String bodyType = res.getString("body_type");
				boolean isAutomatic = res.getBoolean("is_automatic");
				Double dailyRate = res.getDouble("daily_rate");
				carType = new CarTypeDTO(id, typeClass, make_model, bodyType, isAutomatic, dailyRate); 
			
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return carType;
	}

//	Display all details of the car in the booking table
//	select * from car_type t left join car c on t.car_type_id = c.car_type right join booking b on b.car_id = c.car_id;
}
